How To Read CSV Files In Python (Module, Pandas, & Jupyter Notebook Examples)

CSV (Comma Separated Values) is a common file format (text file) used for storing and exchanging tabular data. It consists of rows and columns, where each row represents a record and each column represents a field. CSV files are easy to create, read, and manipulate, and can be opened in most spreadsheet programs. Noteable allows leveraging plain text files (csv) and complex data.

Python provides a built-in csv module (regular reader) for reading CSV files. The csv module provides functions like csv.reader() and csv.DictReader() that can be used to read CSV files line-by-line or as a dictionary.

Here’s an example of how to read a CSV file using the csv module:

This code opens the data.csv file and creates a csv.reader object. The for loop then iterates over each row in the file, printing it to the console.

Once you have read a CSV file into Python, you can manipulate the data using Python’s built-in data structures like lists, dictionaries, and tuples.

For example, to filter CSV based on a condition, you can use list comprehension. Here’s an example that filters rows from a CSV file where the age field is greater than 30:

This code reads the CSV file using the csv.DictReader() function, which returns each row as a dictionary. The list comprehension then filters the data based on the age field, and the resulting data is stored in the filtered_data variable.

Use the drop_duplicates method to remove duplicate rows:

Save the cleaned data to a new CSV file:

The inplace=True parameter in step 3 modifies the DataFrame itself and removes duplicates. If you prefer to keep the original DataFrame unchanged, you can omit this parameter and assign the cleaned DataFrame to a new variable.

Additionally, you may want to specify which columns should be used to identify duplicates. By default, drop_duplicates considers all columns. To specify columns, you can pass a list of column names to the subset parameter:

This will remove rows that have the same values in both column1 and column2.

While Python’s built-in data structures are useful for small datasets, they can become unwieldy when working with large datasets. This is where the pandas library comes in. Pandas is a powerful library for data manipulation and analysis, and it provides a DataFrame object that makes it easy to work with CSV data.

To use pandas, you need to first install it using pip, then:

Use the to_json method to convert the DataFrame to a JSON object:

In the to_json method, orient=’records’ specifies that each row in the DataFrame should be converted to a JSON object. Other possible values for orient include ‘index’, ‘columns’, and ‘values’.

Write the JSON object to a file:

This will create a new file named output_file.json in the current working directory and write the JSON string to it.

Alternatively, you can use the to_json method directly to write the JSON object to a file:

Learn more about json loads too slow? Get up to 100x faster json loading with these 4 alternatives to the standard json library in Python.

Load the CSV files into pandas DataFrames:

You will need to load all the CSV files you want to merge in separate DataFrames. Make sure that the column names and data types are consistent across all files.

Concatenate the DataFrames using the concat function:

The concat function combines the DataFrames along a given axis (by default, axis=0, meaning they are concatenated vertically). The function takes a list of DataFrames as its first argument.

Write the merged DataFrame to a new CSV file:

The index=False parameter specifies that the row index should not be included in the output file.

Optionally, you can also use the merge method instead of concat if you want to merge DataFrames based on a common column. Here’s an example:

In this example, merge combines the DataFrames based on the values in the common_column column.

To select columns of a pandas DataFrame from a CSV file in Python, you can read the CSV file into a DataFrame using the read_csv() function provided by Pandas and then select the desired columns using their names or indices. Here’s an example of how to select columns from a CSV file:

In this example, we first read a CSV file named ‘data.csv’ into a DataFrame df using the read_csv() function. We then select specific columns from the DataFrame df using their names or indices. The df[[‘Name’, ‘Age’]] statement selects the ‘Name’ and ‘Age’ columns by name, while the df.iloc[:, [0, 2]] statement selects the first and third columns (i.e., ‘Name’ and ‘Salary’) by index.

After selecting the desired columns, we export the resulting DataFrame to a new CSV file named ‘selected_data.csv’ using the to_csv() function. The index=False parameter specifies that we do not want to write the row index to the CSV file.

Filter the data based on your criteria. You can filter CSV data using Python by reading the CSV file into a pandas DataFrame and then using the various methods available in pandas to filter the data. Here’s an example:

In this example, replace ‘data.csv’ with the filename of your CSV file and ‘column_name’ and ‘filter_value’ with the name of the column and value you want to filter by. The filtered data will be saved to a new CSV file called ‘filtered_data.csv’. You can add additional conditions by using the & and | operators to combine multiple conditions.

Alternatively, you can also filter CSV data using the built-in csv module in Python. Here’s an example:

In this example, replace ‘data.csv’ with the filename of your CSV file, column_index with the index of the column you want to filter by, and ‘filter_value’ with the value you want to filter by. You can add additional conditions by using the and and or operators to combine multiple conditions.

You can be writing CSV files to an Excel file using Python by using the Pandas library. Pandas provides a simple and efficient way to read data from CSV files and write it to Excel files. Here’s an example code to convert a CSV file to an Excel file using Python:

In the above code, we first import the Pandas library. Then, we read the CSV file into a Pandas DataFrame using the read_csv() function. Next, we write the DataFrame to an Excel file using the to_excel() function. The index=False parameter is used to exclude the index column from being written to the Excel file.

You can customize the code according to your requirements, such as specifying the sheet name, selecting specific columns, formatting the Excel file, and more. Pandas provides various functions and options to customize the output. You can refer to the Pandas documentation for more information.

You can write data to a CSV file using Pandas by using the to_csv() function. Here’s an example code to write data to a CSV file with Pandas:

In the above code, we create a DataFrame with the data using a Python dictionary. Each key in the dictionary represents a column name, and the corresponding value represents the column data.

Next, we write the DataFrame to a CSV file using the to_csv() function. We provide the filename as the first parameter and set the index parameter to False to exclude the index column from the output. Pandas automatically writes the header row based on the DataFrame column names and writes the data rows with the corresponding values.

You can customize the code according to your requirements, such as loading data from a database or a CSV file and transforming it into a DataFrame, or specifying additional options such as the delimiter, encoding, and more. Pandas provides various options and functions to handle different use cases. You can refer to the Pandas documentation for more information.

Here’s a walkthrough example of reading, manipulating, and visualizing CSV data using both the CSV module and pandas library in Jupyter Notebook using Noteable.

With interactive no-code visualization and collaboration features and the ability to use a programming language of choice, Noteable enables you to work with data the way you want. This saves time, and frustration and ensures that data teams don’t have to hop between multiple tools like SQL editor, Python IDE, BI tool, and Slideshow tools to deliver a project end to end.

Noteable is the collaborative data notebook where teams across expertise – from the data curious to data experts – explore data, exchange ideas, and share impactful stories. Deepen collaboration and understanding around your organizational data with a free account today.